package mytest.wd;

import com.github.vertical_blank.sqlformatter.SqlFormatter;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

public class ZipSql {

    public static void main(String[] args) throws IOException {
        String sql = "select registerDate,push_type,\n" +
                "count(distinct t2.phone) as '注册人数',\n" +
                "count(distinct t4.user_gid) as '进件人数',\n" +
                "count(distinct t5.user_gid) as '获额人数',\n" +
                "count(distinct t6.user_gid) as '发起提现人数',\n" +
                "count(distinct t7.user_gid) as '交易人数'\n" +
                "from\n" +
                "(select phone,push_type from ngn_test.earned_marketing_202407171) t1\n" +
                "inner join (\n" +
                "select a.user_gid,AES_DECREPT_TZ2(b.hash_mobile) as phone,\n" +
                "DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(register_time), @@session.time_zone, 'Africa/Lagos'), '%Y%m%d') as registerDate\n" +
                "from business.ostrader.user_lightning a\n" +
                "inner join business.ospanda.user_base b on a.user_gid = b.gid\n" +
                ") t2 on t1.phone = t2.phone\n" +
                "left join (select user_gid, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(create_time), @@session.time_zone, 'Africa/Lagos'), '%Y%m%d') as createDate from business.smile.user_credit_record where auth_type=136) t4\n" +
                "on t2.user_gid = t4.user_gid and t2.registerDate = t4.createDate\n" +
                "left join (select user_gid, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(create_time), @@session.time_zone, 'Africa/Lagos'), '%Y%m%d') as createDate from business.ostrader.log_credit_line where current_credit_line>0) t5\n" +
                "on t4.user_gid = t5.user_gid and t4.createDate = t5.createDate\n" +
                "left join (select user_gid, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(create_time), @@session.time_zone, 'Africa/Lagos'), '%Y%m%d') as createDate from business.ostrader.record_loan_apply) t6\n" +
                "on t5.user_gid = t6.user_gid and t5.createDate = t6.createDate\n" +
                "left join (select user_gid, DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(create_time), @@session.time_zone, 'Africa/Lagos'), '%Y%m%d') as createDate from business.ostrader.record_loan where loan_status = 6) t7\n" +
                "on t6.user_gid = t7.user_gid and t6.createDate = t7.createDate\n" +
                "where registerDate = '20240717'\n" +
                "group by registerDate,push_type  order by registerDate desc,push_type asc";
        String s = zipSql(sql);
        System.out.println(s);
    }

    public static String zipSql(String sql) throws IOException {
        sql =  beautifulSql(sql);

        byte[] sqlBytes = sql.getBytes(StandardCharsets.UTF_8);
        //字节流
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(sqlBytes);
        //字节流转成字符流
        InputStreamReader inputStreamReader = new InputStreamReader(byteArrayInputStream);
        BufferedReader bufferedReader = new BufferedReader(inputStreamReader);

        String line;
        StringBuilder stringBuilder = new StringBuilder();
        while ((line = bufferedReader.readLine()) != null) {
            stringBuilder.append(" ").append(line.trim());
        }
        return stringBuilder.toString();
    }

    public static String beautifulSql(String sql) {
        return SqlFormatter.format(sql);
    }

}
